﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sofire.Data;

namespace SofireXSqlEntityGenerator.Core
{
    /*
     对视图的支持，还没尽兴测试
     */
    public abstract class SqlGeneratorBase
    {
        private QueryEngineBase _Engine;
        public QueryEngineBase Engine
        {
            get
            {
                return this._Engine;
            }
        }

        public SqlGeneratorBase(QueryEngineBase engine)
        {
            if(engine == null) throw new ArgumentNullException("engine");
            this._Engine = engine;
        }

        private void CreateWhereSql(StringBuilder builder, string[] whereColumns)
        {
            if(whereColumns.Length > 0)
            {
                builder.AppendLine("WHERE");
                builder.Append("  ");
                builder.Append(whereColumns[0]);
                builder.Append("=");
                builder.Append(_Engine.ParameterPrefixWithText);
                var length = whereColumns.Length - 1;
                if(length == 0)
                {
                    builder.Append(whereColumns[0]);
                }
                else
                {
                    builder.AppendLine(whereColumns[0]);
                }
                for(int i = 1 ; i < whereColumns.Length ; i++)
                {
                    builder.Append("  AND ");
                    builder.Append(whereColumns[i]);
                    builder.Append("=");
                    builder.Append(_Engine.ParameterPrefixWithText);
                    builder.Append(whereColumns[i]);
                    if(i < length)
                    {
                        builder.AppendLine();
                    }
                }
            }
        }

        private string CreateSelectSql(string tableName, string[] columns, string[] whereColumns)
        {
            /* SELECT
             *   UserID
             *   ,UserName
             *   ,Password
             *   ,Memo
             * FROM MyUsers
             * WHERE
             *   UserID=@UserID
             *   AND UserName=@UserName*/
            StringBuilder builder = new StringBuilder();
            builder.AppendLine("SELECT");
            if(columns.Length == 0)
            {
                builder.Append("  *");
            }
            else
            {
                builder.Append("  ");
                builder.AppendLine(columns[0]);
                for(int i = 1 ; i < columns.Length ; i++)
                {
                    builder.Append("  ,");
                    builder.AppendLine(columns[i]);
                }
            }

            builder.Append("FROM ");
            if(whereColumns == null || whereColumns.Length == 0)
            {
                builder.Append(tableName);
            }
            else
            {
                builder.AppendLine(tableName);
                this.CreateWhereSql(builder, whereColumns);
            }

            return builder.ToString();
        }
        private string CreateInsertSql(string tableName, string[] columns)
        {
            /*
             * INSERT INTO MyUsers
             *   (UserID
             *   ,UserName
             *   ,Password
             *   ,Memo)
             * VALUES
             *   (@UserID
             *   ,@UserName
             *   ,@Password
             *   ,@Memo)
             */
            StringBuilder builder = new StringBuilder();
            builder.Append("INSERT INTO ");
            builder.AppendLine(tableName);
            var length = columns.Length - 1;

            builder.Append("  (");

            builder.AppendLine(columns[0]);
            for(int i = 1 ; i < length ; i++)
            {
                builder.Append("  ,");
                builder.AppendLine(columns[i]);
            }
            builder.Append("  ,");
            builder.Append(columns[length]);
            builder.AppendLine(")");

            builder.AppendLine("VALUES");
            builder.Append("  (");
            builder.Append(_Engine.ParameterPrefixWithText);
            builder.AppendLine(columns[0]);
            for(int i = 1 ; i < length ; i++)
            {
                builder.Append("  ,");
                builder.Append(_Engine.ParameterPrefixWithText);
                builder.AppendLine(columns[i]);
            }
            builder.Append("  ,");
            builder.Append(_Engine.ParameterPrefixWithText);
            builder.Append(columns[length]);
            builder.Append(")");

            return builder.ToString();
        }
        private string CreateUpdateSql(string tableName, string[] columns, string[] whereColumns)
        {
            /*
             * UPDATE MyUsers SET
             *   UserName=@UserName
             *   ,Password=@Password
             * WHERE
             *   UserID=@UserID
             */
            StringBuilder builder = new StringBuilder();
            builder.Append("UPDATE ");
            builder.Append(tableName);
            builder.AppendLine(" SET");

            builder.Append("  ");
            builder.Append(columns[0]);
            builder.Append("=");
            builder.Append(_Engine.ParameterPrefixWithText);
            builder.AppendLine(columns[0]);
            for(int i = 1 ; i < columns.Length ; i++)
            {
                builder.Append("  ,");
                builder.Append(columns[i]);
                builder.Append("=");
                builder.Append(_Engine.ParameterPrefixWithText);
                if(i == columns.Length - 1 && (whereColumns == null || whereColumns.Length == 0))
                {
                    builder.Append(columns[i]);
                }
                else builder.AppendLine(columns[i]);
            }
            this.CreateWhereSql(builder, whereColumns);
            return builder.ToString();
        }
        private string CreateDeleteSql(string tableName, string[] whereColumns)
        {
            /*
             * DELETE
             *   MyUsers
             * WHERE
             *   UserID=@UserID
             */
            StringBuilder builder = new StringBuilder();

            if(whereColumns == null || whereColumns.Length == 0)
            {
                builder.Append("DELETE ");
                builder.Append(tableName);
            }
            else
            {
                builder.AppendLine("DELETE ");
                builder.Append("  ");
                builder.AppendLine(tableName);
                this.CreateWhereSql(builder, whereColumns);
            }
            return builder.ToString();
        }

        public abstract TableResult GetUserTables();
        public abstract TableResult GetUserViews();
        public abstract TableResult GetTableColumns(string tableName);

        public TableResult GetEmptyTable(string tableName)
        {
            return this.Engine.ExecuteTable("SELECT * FROM " + tableName + " WHERE 1=2");
        }

        public string CreateSql(SqlOptions options)
        {
            var columns = options.Columns ?? new string[0];
            var whereColumns = options.WhereColumns ?? new string[0];
            switch(options.Operator)
            {
                case SqlOperator.Select:
                    return this.CreateSelectSql(options.TableName, columns, whereColumns);

                case SqlOperator.Insert:
                    if(columns.Length == 0) return string.Empty;
                    return this.CreateInsertSql(options.TableName, columns);

                case SqlOperator.Update:
                    if(columns.Length == 0) return string.Empty;
                    return this.CreateUpdateSql(options.TableName, columns, whereColumns);

                case SqlOperator.Delete:
                    return this.CreateDeleteSql(options.TableName, whereColumns);
            }
            throw new NotImplementedException();
        }

    }
}
